﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class WorksDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoWorks model)
        {
            model.WOR_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Works]");
            sql.Append("([WOR_ID],[WOR_TITLE],[WOR_ISGROUP],[WOR_GROUPS],[WOR_PERTICIPANTS],[WOR_COMMENT],[WOR_TYPE],[WOR_URL],[WOR_DATE],[WOR_CONTESTS],[WOR_STUDENTNAME])");
            sql.Append("VALUES");
            sql.Append("(@WOR_ID,@WOR_TITLE,@WOR_ISGROUP,@WOR_GROUPS,@WOR_PERTICIPANTS,@WOR_COMMENT,@WOR_TYPE,@WOR_URL,@WOR_DATE,@WOR_CONTESTS,@WOR_STUDENTNAME)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoWorks model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Works] SET ");
            sql.Append("WOR_TITLE=@WOR_TITLE");
            sql.Append(",WOR_ISGROUP=@WOR_ISGROUP");
            sql.Append(",WOR_GROUPS=@WOR_GROUPS");
            sql.Append(",WOR_PERTICIPANTS=@WOR_PERTICIPANTS");
            sql.Append(",WOR_COMMENT=@WOR_COMMENT");
            sql.Append(",WOR_STUDENTNAME=@WOR_STUDENTNAME");
            sql.Append(",WOR_TYPE=@WOR_TYPE");
            sql.Append(",WOR_URL=@WOR_URL");
            sql.Append(",WOR_DATE=@WOR_DATE");
            sql.Append(",WOR_CONTESTS=@WOR_CONTESTS");
            sql.Append(" WHERE ");
            sql.Append("WOR_ID=@WOR_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }
       
        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string wOR_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM Works");
            sql.Append(" WHERE ");
            sql.Append("WOR_ID=@WOR_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@WOR_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = wOR_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>.
        public BoWorks GetModel(string wOR_ID)
        {
            System.Text.StringBuilder sql = new StringBuilder();
            sql.Append("SELECT WOR_ID,WOR_TITLE,WOR_ISGROUP,WOR_GROUPS,WOR_PERTICIPANTS,WOR_COMMENT,WOR_TYPE,WOR_URL,WOR_DATE,WOR_CONTESTS,WOR_STUDENTNAME,STU_NAME, GRO_NAME , CON_NAME  FROM Works AS W LEFT JOIN (Participants AS P  LEFT JOIN Contests AS CON ON CON.CON_ID=P.PAR_CONTESTS LEFT JOIN Groups AS G ON P.PAR_GROUPS=G.GRO_ID LEFT JOIN Students AS S ON P.PAR_STUDENTS=S.STU_ID ) ON W.WOR_CONTESTS=P.PAR_CONTESTS and P.PAR_AWARDS!='-1' ");
            sql.Append(" WHERE WOR_ID=@WOR_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@WOR_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = wOR_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoWorks> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoWorks> GetAllWorks()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT WOR_ID,WOR_TITLE,WOR_ISGROUP,WOR_GROUPS,WOR_PERTICIPANTS,WOR_COMMENT,WOR_TYPE,WOR_URL,WOR_DATE,WOR_CONTESTS ,WOR_STUDENTNAME,STU_NAME, GRO_NAME , CON_NAME  FROM Works AS W LEFT JOIN (Participants AS P  LEFT JOIN Contests AS CON ON CON.CON_ID=P.PAR_CONTESTS LEFT JOIN Groups AS G ON P.PAR_GROUPS=G.GRO_ID LEFT JOIN Students AS S ON P.PAR_STUDENTS=S.STU_ID ) ON W.WOR_CONTESTS=P.PAR_CONTESTS and P.PAR_AWARDS!='-1' ");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoWorks> list = GetList(table);
            return list;
        }

        public IList<BoWorks> GetContestWorks(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT WOR_ID,WOR_TITLE,WOR_ISGROUP,WOR_GROUPS,WOR_PERTICIPANTS,WOR_COMMENT,WOR_TYPE,WOR_URL,WOR_DATE,WOR_CONTESTS ,WOR_STUDENTNAME,STU_NAME, GRO_NAME , CON_NAME  FROM Works AS W LEFT JOIN (Participants AS P  LEFT JOIN Contests AS CON ON CON.CON_ID=P.PAR_CONTESTS LEFT JOIN Groups AS G ON P.PAR_GROUPS=G.GRO_ID LEFT JOIN Students AS S ON P.PAR_STUDENTS=S.STU_ID ) ON W.WOR_CONTESTS=P.PAR_CONTESTS and P.PAR_AWARDS!='-1' ");
            sql.Append(" WHERE P.PAR_CONTESTS=@CON_ID ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CON_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoWorks> list = GetList(table);
            return list;
        }

        private IList<BoWorks> GetList(DataTable table)
        {
            List<BoWorks> list = new List<BoWorks>();
            if (table != null && table.Rows.Count > 0)
            {
                
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoWorks model = new BoWorks();
                    model.WOR_ID = (string)table.Rows[i]["WOR_ID"];
                    model.WOR_TITLE = (string)table.Rows[i]["WOR_TITLE"];
                    model.WOR_ISGROUP = (Int32)table.Rows[i]["WOR_ISGROUP"];
                    model.WOR_GROUPS = table.Rows[i]["WOR_GROUPS"] == DBNull.Value?"":(string)table.Rows[i]["WOR_GROUPS"];
                    model.WOR_PERTICIPANTS = table.Rows[i]["WOR_PERTICIPANTS"] == DBNull.Value ? "" : (string)table.Rows[i]["WOR_PERTICIPANTS"];
                    model.WOR_COMMENT = table.Rows[i]["WOR_COMMENT"] == DBNull.Value ? "" : (string)table.Rows[i]["WOR_COMMENT"];
                    model.WOR_TYPE = (Int32)table.Rows[i]["WOR_TYPE"];
                    model.WOR_URL = (string)table.Rows[i]["WOR_URL"];
                    model.WOR_DATE = (DateTime)table.Rows[i]["WOR_DATE"];
                    model.WOR_STUDENTNAME = table.Rows[i]["WOR_STUDENTNAME"] == DBNull.Value ? "" : (string)table.Rows[i]["WOR_STUDENTNAME"];
                    model.WOR_CONTESTS = table.Rows[i]["WOR_CONTESTS"] == DBNull.Value ? "" : (string)table.Rows[i]["WOR_CONTESTS"];                    
                    model.CON_NAME = table.Rows[i]["CON_NAME"] == DBNull.Value ? "" : (string)table.Rows[i]["CON_NAME"];
                    model.GRO_NAME = table.Rows[i]["GRO_NAME"] == DBNull.Value ? "" : (string)table.Rows[i]["GRO_NAME"];
                    model.GRO_NAME = table.Rows[i]["STU_NAME"] == DBNull.Value ? "" : (string)table.Rows[i]["STU_NAME"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoWorks model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@WOR_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@WOR_TITLE",SqlDbType.NVarChar,50),
                new SqlParameter("@WOR_ISGROUP",SqlDbType.Int,4),
                new SqlParameter("@WOR_GROUPS",SqlDbType.NVarChar,50),
                new SqlParameter("@WOR_PERTICIPANTS",SqlDbType.NVarChar,50),
                new SqlParameter("@WOR_COMMENT",SqlDbType.Text),
                new SqlParameter("@WOR_TYPE",SqlDbType.Int,4),
                new SqlParameter("@WOR_URL",SqlDbType.NVarChar,50),
                new SqlParameter("@WOR_DATE",SqlDbType.DateTime,8),
                new SqlParameter("@WOR_CONTESTS",SqlDbType.NVarChar,50),
                new SqlParameter("@WOR_STUDENTNAME",SqlDbType.NVarChar,50)
            };
            parameters[0].Value = model.WOR_ID;
            parameters[1].Value = model.WOR_TITLE;
            parameters[2].Value = model.WOR_ISGROUP;
            parameters[3].Value = model.WOR_GROUPS;
            parameters[4].Value = model.WOR_PERTICIPANTS;
            parameters[5].Value = model.WOR_COMMENT;
            parameters[6].Value = model.WOR_TYPE;
            parameters[7].Value = model.WOR_URL;
            parameters[8].Value = model.WOR_DATE;
            parameters[9].Value = model.WOR_CONTESTS;
            parameters[10].Value = model.WOR_STUDENTNAME;
           
            return parameters;
        }
    }
}